select /*+ SET_VAR(exec_mem_limit = 3669654427) */
		ad_disptype
		,business_line
		,platform
		,scate2
		,sloc1
		,slot
		,search_pv
		,search_uv
		,search_as
		,search_epv
                ,round(if(search_as / search_epv is null , 0 , search_as / search_epv),5) as search_asn
	from (
		select
			ad_disptype
			,business_line
			,platform
			,scate2
			,sloc1
			,slot
			,APPROX_COUNT_DISTINCT(recsid) as search_pv
			,APPROX_COUNT_DISTINCT(case when platform = '3' and length(imei) > 5 then imei when platform in('1','2') and length(cookieid) >5 then cookieid else null end) as search_uv
			,APPROX_COUNT_DISTINCT(concat_ws(',',recsid,slot,ad_id)) as search_as
			,APPROX_COUNT_DISTINCT(if(parthasinfo=1,recsid,null)) search_epv
			-- ,APPROX_COUNT_DISTINCT(concat_ws(',',recsid,slot,ad_id)) /APPROX_COUNT_DISTINCT(if(parthasinfo=1,recsid,null))  as search_asn
		from (
			SELECT
				case WHEN ad_disptype = NULL then '-' else ad_disptype END as ad_disptype
				,case WHEN  business_line = NULL then '-' else business_line end as business_line
				,case WHEN  platform = NULL then '-' else platform end as platform
				,case WHEN  scate2 = NULL  then '-' else scate2 end as scate2
				,case WHEN  sloc1 = NULL  then '-' else sloc1 end as sloc1
				,case WHEN  slot = NULL then '-' else slot end as slot
				,recsid
				,parthasinfo
				,imei_md5 as imei
				,cookieid
				,ad_id
			from hdp_lbg_supin_dwd_zp_search
			where (e_time) >= '%s' and (e_time) <= '%s'
		) tmp
		GROUP by
		GROUPING SETS (
			(ad_disptype)
			,(business_line)
			,(platform)
			,(scate2)
			,(sloc1)
			,(slot)
                        ,()
			,(ad_disptype,business_line)
			,(ad_disptype,platform)
			,(business_line,platform)
			,(ad_disptype,scate2)
			,(business_line,scate2)
			,(platform,scate2)
			,(ad_disptype,sloc1)
			,(business_line,sloc1)
			,(platform,sloc1)
			,(scate2,sloc1)
			,(ad_disptype,slot)
			,(business_line,slot)
			,(platform,slot)
			,(scate2,slot)
			,(sloc1,slot)
			,(business_line,platform,slot)
			,(ad_disptype,business_line,platform,slot)
			,(business_line,platform,scate2,slot)
			,(business_line,platform,sloc1,slot)
			,(ad_disptype,business_line,platform,scate2)
			,(ad_disptype,business_line,platform,sloc1)
			,(ad_disptype,business_line,platform,scate2,slot)
			,(ad_disptype,business_line,platform,sloc1,slot)
			,(ad_disptype,business_line,platform,scate2,sloc1,slot)
		)
		HAVING(
			if(ad_disptype is null ,'',ad_disptype) != '-'
			and if(business_line is null ,'',business_line) != '-'
			and if(platform is null ,'',platform) != '-'
			and if(scate2 is null ,'',scate2) != '-'
			and if(sloc1 is null ,'',sloc1) != '-'
			and if(slot is null ,'',slot) != '-'
		)
	) tmp